const config = require("./../../config")
const mysql = require("mysql")
const querystring = require('querystring')
const schedule = require('node-schedule');


const pool1 = mysql.createPool({
  host     :  config.database1.HOST,
  user     : config.database1.USERNAME,
  password : config.database1.PASSWORD,
  database : config.database1.DATABASE
})

const pool2 = mysql.createPool({
  host     :  config.database2.HOST,
  user     : config.database2.USERNAME,
  password : config.database2.PASSWORD,
  database : config.database2.DATABASE
})


let query = function( sql, values ) {

  return new Promise(( resolve, reject ) => {
    pool1.getConnection(function(err, connection) {
      if (err) {
        resolve( err )
      } else {
        connection.query(sql, values, ( err, rows) => {

          if ( err ) {
            reject( err )
          } else {
            resolve( rows )
            // console.log(rows)
          }
          connection.release()
        })
      }
    })
  })

}

let query1 = function( sql, values ) {

  return new Promise(( resolve, reject ) => {
    pool2.getConnection(function(err, connection) {
      if (err) {
        resolve( err )
      } else {
        connection.query(sql, values, ( err, rows) => {

          if ( err ) {
            reject( err )
          } else {
            resolve( rows )
            // console.log(rows)
          }
          connection.release()
        })
      }
    })
  })

}

let WeekAgotime = Math.round(new Date().getTime()/1000) - 24 * 60 * 60 * 15;
let _WeekAgotime = WeekAgotime


//公用方法
const transform={
  //备份时间点
  async excuteDate(table,keys,time){
    let _sql = `SELECT * FROM ?? WHERE tbDeviceRec.time > ? LIMIT 1`
    return query(_sql, keys, table, time)
},
  
  //第一条记录
  async getFirstRec(table, keys){
    let _sql = `select ?? from ?? limit 1`
    return query(_sql, keys, table)
},
  // const parsequery = function(json, Key){
  //   let queryArr = [];
  // 　for(var i=0;i<json.length;i++){
  // 　　　for(var key in json[i]){
  //         if (key == Key) {
  //           queryArr.push(json[i][key])
  //         }
  //     }
  // 　}
  // console.log(queryArr.toString())
  // return  queryArr.toString()
  // }
  
  // let id = parsequery(json, 'id')
  // let devno = parsequery(json, 'devno')
  // let content = parsequery(json, 'content')
  // let type = parsequery(json, 'type')
  // let time = parsequery(json, 'time')
  // let note = parsequery(json, 'note')

  // let querystring = id + devno + content + type + time + note
  // console.log(querystring)


}






const tbDeviceRec = {
  async excuteDate(){
    let _sql = `SELECT time FROM tbDeviceRec WHERE tbDeviceRec.time > ? LIMIT 1`
    return query(_sql, _WeekAgotime)
  },
  async getFirstRec(){
    let _sql = `select * from tbDeviceRec limit 1`
    return query(_sql)
  },
    //查旧表 
  async queryOldTable(){
    let id = await tbDeviceRec.getFirstRec('id')
    let time = await tbDeviceRec.excuteDate('time','tbDeviceRec',_WeekAgotime)
    id = id[0].id
    time = time[0].time
    let _sql = `SELECT * FROM tbDeviceRec WHERE tbDeviceRec.time < ? ORDER BY id LIMIT 1000`
    return query(_sql, time)
  },

  async insertData() {
    let data = await tbDeviceRec.queryOldTable()
    let string=JSON.stringify(data)
    let json =  JSON.parse(string)
    // 拼接查询

    var parsequery = function(json){
      let queryArr = ""
    　for(var i=0;i<json.length;i++){
          var one = ""
    　　　for(var key in json[i]){
            var str = "'" + json[i][key] + "'"
            // console.log(str)
            one = one == "" ? "(" + json[i][key] : one  + ","  + str  ;
        }
        one = one + ")"
        queryArr = queryArr == "" ? one : queryArr + "," + one
      }
      // console.log(queryArr)
      return queryArr
    }
    json = parsequery(json)

   // let transArr = parsequery(json)

   //  function chunk(arr, size) {
   //    var arr2=[];
   //    for(var i=0;i<arr.length;i=i+size){
   //      arr2.push(arr.slice(i,i+size));
   //    }
   //    return arr2;
   //  }
   //  let querystring = chunk(transArr,6)
    console.log(json)
    
    let _sql = "INSERT INTO `tbDeviceRec_copy`(`id`,`devno`,`content`,`type`,`time`,`note`) VALUES " + json
    // let _sql = "INSERT INTO tbDeviceRec_copy SET ?"
    // return query( _sql,(id,devno,content,type,time,note))
    return query1( _sql)
  },


  async deleteData () {
    let time = await tbDeviceRec.excuteDate()
    time = time[0].time
    let deleteData = await tbDeviceRec.insertData()
    console.log('success')
    let _sql = `DELETE FROM tbDeviceRec WHERE tbDeviceRec.time < ? ORDER BY id LIMIT 1000`
    return query( _sql, time )
},

  async count(){
    await tbDeviceRec.deleteData()
    let firstDate = await tbDeviceRec.getFirstRec('time')
    let time = await tbDeviceRec.excuteDate(_WeekAgotime)
    time = time[0].time
    firstDate = firstDate[0].time
    firstDate < time ? tbDeviceRec.count() : (()=>{
      console.log('tbDeviceRec备份完成');
    })()
  },
}

tbDeviceRec.count()

// schedule.scheduleJob('0 30 18 * * *',function(){
//     console.log('scheduleCronstyle:'+new Date())
//     tbDeviceRec.count()  //每天1:30执行
// })


module.exports = {tbDeviceRec}


